package com.info.zhiduoduo.repository.mapper.crowdsource;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.crowdsource.*;
import com.info.zhiduoduo.repository.entity.crowdsource.CrowdsourceInvoice;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 众包自然人发票 Mapper 接口
 * </p>
 *
 * @author cuichenglong
 * @since 2020-08-24
 */
public interface CrowdsourceInvoiceMapper extends BaseMapper<CrowdsourceInvoice> {

    @Select(" SELECT * FROM zdd_crowdsource_invoice WHERE projectid = #{projectid} AND state IN ( 2, 3 )  AND contract_path != '' ")
    List<CrowdsourceInvoice> getPushedContractInvoice(int projectid);

    /**
     * 该创客目前参与的 未完成开票的众包项目个数（除去已作废的开票信息）
     * @param userid 创客userid
     * @return int
     */
    @Select(" SELECT count( * ) FROM zdd_crowdsource_invoice ci " +
            " LEFT JOIN zdd_crowdsource_project_item cpi ON cpi.projectid = ci.projectid  " +
            " WHERE " +
            " cpi.userid = #{userid} AND ci.crowdsourceid = #{userid} " +
            " AND cpi.user_type = 2 AND ci.state NOT IN ( 3, 4 ) ")
    int notCrowdSourceProjectInvoicedCountByUserid(@Param("userid") int userid);

    /**
     * 智众包成本发票列表
     *
     * @param userType
     * @param startDate
     * @param endDate
     * @return
     */
    @Select("<script>" +
            " SELECT zci.*,cspi.project_service_charge,cspi.integrated_tax_fee,zms.tax_acct_state, " +
            " ccp.company_name AS subjectName, cc.name as invoiceCityName,zsm.managername," +
            " zms.username studioName,ca.strategy_invoice as companyStrategyInvoice," +
            " case when zcit.id is not null then 1 else 0 end unInvoiceRemind " +
            " FROM zdd_crowdsource_invoice zci " +
            " LEFT JOIN zdd_crowdsource_project csp ON csp.projectid = zci.projectid AND zci.project_type = 2 and csp.state = 4 AND csp.deleted = 0  " +
            " LEFT JOIN zdd_crowdsource_project_item cspi ON cspi.id = zci.project_item_id AND zci.project_type = 2 " +
//            " LEFT JOIN zdd_entrustment_project ep ON ep.id = zci.projectid AND zci.project_type = 6 " +
            " LEFT JOIN zdd_crowdsource_company_party ccp ON ccp.id = zci.party_id " +
            " LEFT JOIN zdd_system_manager zsm ON zsm.managerid = zci.operator  " +
            " LEFT JOIN zdd_city_config cc ON cc.id = zci.invoice_city_id " +
//            " LEFT JOIN zdd_company c ON c.compid = zci.compid " +
            " LEFT JOIN zdd_company_account ca ON ca.compid = zci.compid " +
            " LEFT JOIN zdd_maker_account zma ON zma.userid = zci.crowdsourceid AND zci.user_type = 2 " +
            " LEFT JOIN zdd_maker_studio zms ON zms.userid = zci.crowdsourceid AND zci.user_type = 2 " +
            " left join (SELECT zci.id,zci.crowdsourceid,zci.project_amount,zci.createtime,zci.real_name,zci.compname,zms.username,zms.tax_acct_state " +
            "           FROM zdd_crowdsource_invoice zci " +
            "           JOIN zdd_maker_studio zms ON zms.userid = zci.crowdsourceid " +
            "           where zci.state =  1 AND zci.user_type = 2 " +
            "           HAVING case when tax_acct_state = 3 then createtime &lt; #{overdueNaturalDay} " +
            "           else createtime &lt; #{overdueWorkDay} end ) zcit on zci.id = zcit.id" +
            " WHERE zci.createtime between #{startDate} and #{endDate} " +
            " AND zci.user_type = #{userType} " +
            " AND zci.compname like '%${compname}%' " +
            " AND CONCAT(zci.real_name, ifnull(zms.username, '')) like '%${username}%' " +
            " AND ccp.company_name  like '%${operatingSubject}%' " +
            " AND if(#{state} != 0, zci.state = #{state}, 1 = 1) " +
            " AND IF(#{strategy} != 1, " +
            "   IF(zci.strategy = 1 and zci.state = 1, DATE(zci.strategy_date) &lt;= CURDATE(), 1=1), " +
            "   zci.strategy = 1 and DATE(zci.strategy_date) &gt;CURDATE() ) " +
            " <if test=\"city != null and city != '' \"> " +
            "   AND IF(zci.user_type = 1, 1=1, zma.opening_location in (${city}) ) " +
            " </if>" +
            " <if test=\"startAmount != null and startAmount != '' \">AND zci.project_amount &gt;= ${startAmount}</if>" +
            " <if test=\"endAmount != null and endAmount != '' \">AND zci.project_amount &lt;= ${endAmount}</if>" +
            " ORDER BY " +
            " <choose>" +
            "  <when test=\"sort != null and sort != '' \">zci.project_amount ${sort} </when> " +
            "  <otherwise>zci.createtime desc </otherwise> " +
            " </choose>" +
            "</script>")
    List<CrowdsourceCostInvoicefDto> crowdsourceCostInvoiceList(@Param("userType") int userType, @Param("compname") String compname, @Param("username") String username,
                                                                @Param("startDate") Date startDate, @Param("endDate") Date endDate,
                                                                @Param("startAmount") String startAmount, @Param("endAmount") String endAmount,
                                                                @Param("state") int state, @Param("strategy") int strategy, @Param("city") String city,
                                                                @Param("sort") String sort,@Param("overdueNaturalDay")Date overdueNaturalDay,@Param("overdueWorkDay")Date overdueWorkDay
                                                                ,@Param("operatingSubject") String operatingSubject);

    //统计成本票创客未开票总金额、未开票总数量；已开税户的按4个工作日预警，未开税户的超过30天预警
    @Select("select count(id) as unInvoicedNum,sum(project_amount) as unInvoicedAmount from ( " +
            "   SELECT zci.id,zci.crowdsourceid,zci.project_amount,zci.createtime,zci.real_name,zci.compname,zms.username,zms.tax_acct_state " +
            "   FROM zdd_crowdsource_invoice zci " +
            "   JOIN zdd_maker_studio zms ON zms.userid = zci.crowdsourceid " +
            "   where zci.state =  1 AND zci.user_type = 2 " +
            "   HAVING case when tax_acct_state = 3 then createtime < #{overdueNaturalDay}  " +
            "          else createtime < #{overdueWorkDay} end " +
            " )data ")
    CrowdsourceCostInvoiceRemindDto costInvoiceUnInvoicedList(Date overdueWorkDay, Date overdueNaturalDay);

    //众包待确认和开票中发票按运营主体统计总金额，总条数
    @Select("SELECT partyId,partyName,count(id) as unInvoicedNum,sum(invoice_amount) as unInvoicedAmount from( " +
            " select i.id,i.projectid,i.projectname,i.invoice_amount,i.project_type,i.state,i.apply_invoice_date, " +
            " case when i.project_type = 2 then cp.party_id else i.userid end as partyId, " +
            " case when i.project_type = 2 then ccp.company_name else ccps.company_name end as partyName " +
            " from zdd_invoice i " +
            " left join zdd_crowdsource_project cp on cp.projectid = i.projectid and i.project_type = 2 " +
            " left join zdd_crowdsource_company_party ccp on ccp.id = cp.party_id and i.project_type = 2 " +
            " left join zdd_crowdsource_company_party ccps on ccps.id = i.userid and i.project_type in (6,8) " +
            " where i.project_type in (0${projectTypes}) " +
            " and i.state in (2,3) " +
            " and (i.apply_invoice_date <= #{currentMonth} or i.apply_invoice_date is null) " +
            ")data group by partyid ")
    List<CrowdsourceInvoiceRemindDto> crowdsourceUnInvoicedList(String projectTypes, Date currentMonth);

   @Select(" SELECT id FROM ( " +
            " SELECT * from zdd_crowdsource_invoice i " +
            " WHERE crowdsourceid = #{crowdsourceid} " +
            " GROUP BY i.createtime asc   " +
            " ) as base   " +
            " GROUP BY crowdsourceid")
    Integer firstBilling(@Param("crowdsourceid") int crowdsourceid);

    /**
     * 包含智众包（成本票）、项目类型
     * @param userId：用户id
     * @param timeType:时间类型：1：当前月度，2：当前季度，3：当前年度，4：当前时间往前的12个月
     * @return
     */
    @Select("select IFNULL(SUM(i.project_amount),0) " +
            " from zdd_crowdsource_invoice i " +
            " where i.project_type in(0${projectTypes}) " +
            " and i.state = 3 " +
            " and i.crowdsourceid = #{userId} and i.user_type = 2" +
            " and " +
            "   CASE " +
            "   when #{timeType} = 1 then YEAR(i.finishtime) = YEAR(NOW()) and MONTH(i.finishtime) = MONTH(NOW()) " +
            "   when #{timeType} = 2 then YEAR(i.finishtime) = YEAR(NOW()) and QUARTER(i.finishtime) = QUARTER(NOW())" +
            "   when #{timeType} = 3 then YEAR(i.finishtime) = YEAR(NOW()) " +
            "   when #{timeType} = 4 then i.finishtime >= DATE_SUB(DATE_FORMAT(CURRENT_DATE(),'%y-%m-01'), INTERVAL 11 MONTH)" +
            "   END")
    BigDecimal getUserCompleteAmount(Integer userId,String projectTypes, Integer timeType);
}
